import pymysql
from pymysql import cursors


def get_connection():
    # 连接数据库
    conn = pymysql.connect(host='localhost',
                           user='root',
                           password='admin',
                           db='cov',
                           cursorclass=cursors.DictCursor,
                           charset='utf8')
    cursor = conn.cursor()
    return conn, cursor


def close_connection(conn, cursor):
    # 关闭数据库
    cursor.close()
    conn.close()


def findAll():
    # 查询所有数据
    conn, cursor = get_connection()
    sql = "SELECT * FROM history order by ds desc"
    cursor.execute(sql)
    result = cursor.fetchall()
    close_connection(conn, cursor)
    return result


# def findByProvince(province):
#     # 根据省份查询数据
#     conn, cursor = get_connection()
#     sql = "SELECT * FROM risk_area WHERE province = %s"
#     cursor.execute(sql, province)
#     result = cursor.fetchall()
#     close_connection(conn, cursor)
#     return result


def delete(id):
    # 根据id删除数据
    conn, cursor = get_connection()
    sql = "DELETE FROM history WHERE id = %s"
    cursor.execute(sql, id)
    conn.commit()
    close_connection(conn, cursor)


# def add(data):
#     # 添加数据
#     conn, cursor = get_connection()
#     sql = "INSERT INTO risk_area (end_update_time, province,city,county,address,type) VALUES (%s, %s, %s, %s, %s, %s)"
#     cursor.execute(sql, [data['end_update_time'], data['province'], data['city'], data['county'], data['address'],
#                          data['type']])
#     conn.commit()
#     close_connection(conn, cursor)


def findById(id):
    # 根据id查询数据
    conn, cursor = get_connection()
    sql = "SELECT * FROM history WHERE id = %s"
    cursor.execute(sql, id)
    result = cursor.fetchone()
    close_connection(conn, cursor)
    return result


def edit(data):
    # 编辑数据
    conn, cursor = get_connection()
    sql = "UPDATE history SET ds=%s, confirm=%s ,confirm_add=%s,confirm_now=%s,suspect=%s," \
          "suspect_add=%s,heal=%s,heal_add=%s,dead=%s,dead_add=%s WHERE id=%s"
    cursor.execute(sql, [data['ds'], data['confirm'], data['confirm_add'], data['confirm_now'], data['suspect'],
                         data['suspect_add'], data['heal'], data['heal_add'], data['dead'], data['dead_add'],
                         data['id']])
    conn.commit()
    close_connection(conn, cursor)
